﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;

/// <summary>
///DBTable 数据表封装类
/// </summary>
namespace MySpace.Data
{
    /// <summary>
    /// 用于封装一条数据记录的类
    /// </summary>
    public class DBTable
    {
        #region 公开属性
        /// <summary>
        /// 当前封装表名
        /// </summary>
        public string TableName { get; set; }
        #endregion

        #region 私有属性
        //获取该条记录的完整SQL语句，如果为空，则不能执行update方法更新数据库
        private string getSQL = string.Empty;
        //被封装的母表
        private DataTable parentTable = null;
        //保存参数列表
        private List<Parameter> parameters = new List<Parameter>();

        
        #endregion

        #region  子类中访问属性
        //数据库访问对象
        protected DBHandler DBHandler { get; set; }
        //所封装的数据行
        protected DataRow DataRow { get; set; }
        //获取记录的条件，如果为空串，则新建记录
        protected string Filter { get; set; }
        //添加参数，获取记录的条件中可能带有参数
        protected void AddParameter(string parameterName, object parameterValue)
        {
            this.parameters.Add(new Parameter(parameterName, parameterValue));
        }
        /// <summary>
        /// 获取数据记录
        /// </summary>
        protected void GetRow()
        {
            bool NullOrEmpty = string.IsNullOrEmpty(this.Filter);//标是否为空记为空，空时候返回true
            if (NullOrEmpty)
                this.getSQL = string.Format("select * from {0}", this.TableName);
            else
                this.getSQL = string.Format("select * from {0} where {1}", this.TableName, this.Filter);
            DBHandler.CommandText = this.getSQL;
            DBHandler.CommandType = CommandType.Text;
            if (NullOrEmpty)
            {
                this.parentTable = DBHandler.GetEmptyTable();
            }
            else
            {
                DBHandler.ClearParameter();
                foreach (Parameter p in this.parameters)
                {
                    this.DBHandler.AddParamerer(p.Name, p.Value);
                }
                this.parentTable = DBHandler.ExecuteDataTable();
            }
            this.parentTable.TableName = this.TableName;
            if (string.IsNullOrEmpty(this.Filter))
            {
                //返回空的数据行
                this.DataRow = this.parentTable.NewRow();
            }
            else
            {
                if (this.parentTable.Rows.Count > 0)
                    this.DataRow = this.parentTable.Rows[0];
                else
                    //若找不到数据行，新建数据行
                    this.DataRow = this.parentTable.NewRow();
            }
        }
        #endregion

        #region 构造方法
        /// <summary>
        /// 构造函数，新建一条记录
        /// </summary>
        /// <param name="dbh">数据库访问类</param>
        /// <param name="tableName">数据表名</param>
        protected DBTable(DBHandler dbh, string tableName)
        {
            this.DBHandler = dbh;
            this.TableName = tableName;
            this.Filter = string.Empty;
            this.GetRow();
        }
        protected DBTable(string tableName)
        {
            this.DBHandler = GetHandler.Init();
            this.TableName = tableName;
            this.Filter = string.Empty;
            this.GetRow();
        }
        protected DBTable(string connectionStringName, string tableName)
        {
            this.DBHandler = GetHandler.Init(connectionStringName);
            this.TableName = tableName;
            this.Filter = string.Empty;
            this.GetRow();

        }
        public DBTable(DataRow dr)
        {
            this.DataRow = dr;
        }
        #endregion

        #region 公开方法
        /// <summary>
        /// 删除表里面的一行数据
        /// </summary>
        /// <returns></returns>
        public virtual bool Delete()
        {
            try
            {
                this.getSQL = string.Format("delete from {0} where {1}",this.TableName,this.Filter);
                DBHandler.CommandText = this.getSQL;
                DBHandler.ExecuteNonQuery();
                this.DataRow.Delete();
                return true;
            }
            catch
            {
                return false;
            }
        }
        /// <summary>
        /// 将记录更新到数据库中
        /// </summary>
        /// <returns></returns>
        public virtual bool Update()
        {
            if (this.parentTable != null && this.getSQL != string.Empty)
            {
                this.DBHandler.CommandText = this.getSQL;
                if (string.IsNullOrEmpty(this.Filter))
                {
                    this.parentTable.Rows.Add(this.DataRow);
                }
                DBHandler.ClearParameter();
                foreach (Parameter p in this.parameters)
                {
                    this.DBHandler.AddParamerer(p.Name, p.Value);
                }
                int r = this.DBHandler.UpdateDate(this.parentTable);
                DBHandler.ClearParameter();
                return (r > 0) ? true : false;
            }
            else
                return false;
        }


        public virtual bool Insert()
        {
            object[] RowItem = this.DataRow.ItemArray;
            string row_value = string.Empty;
            string row_name = string.Empty;
            int i=0,count=0;
            foreach(DataColumn dc in this.parentTable.Columns)
            {
                if (i!=0&&!string.IsNullOrEmpty(RowItem[i].ToString()))
                {
                    if (count!=0)
                    {
                        row_name += ",";
                        row_value += ",";
                    }
                    row_name += "["+dc.ColumnName+"]";
                    switch (dc.DataType.Name.ToLower())
                    {
                        case "string": row_value += "'" + RowItem[i] + "'"; break;
                        case "int": row_value +=  RowItem[i] ; break;
                        case "int32": row_value += RowItem[i]; break;
                        case "datetime": row_value +="#" +RowItem[i]+"#"; break;
                        default: row_value += "'" + RowItem[i] + "'"; break;  
                    }
                    count++;
                }
                i++;
            }
            this.DBHandler.CommandText = string.Format("insert into [{0}]({1}) values({2})", this.TableName, row_name,row_value);
            try
            {
                this.DBHandler.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        #endregion
    }
}